package gbench.appdemo.neo4j;

import org.junit.jupiter.api.Test;

import static gbench.common.matlib.MatlibCanvas.println;
import static gbench.common.tree.LittleTree.IRecord.REC;
import static gbench.common.tree.LittleTree.Term.FT;

import java.io.File;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.LinkedList;
import java.util.List;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import gbench.common.fs.FileSystem;
import gbench.common.fs.XlsFile.SimpleExcel;
import gbench.common.matlib.data.DataReader.DFrame;
import gbench.common.tree.LittleTree;
import gbench.common.tree.LittleTree.CronTime;
import gbench.common.tree.LittleTree.IRecord;
import gbench.common.tree.LittleTree.Json;
import gbench.commonApp.data.DataApp;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;

/**
 * 
 * @author gbench
 *
 */
public class KlcGraph {

    /**
     * 属性集转换为json文件
     * 
     * @param attrs 属性对象
     * @return
     */
    public String attrs2jsonfile(final IRecord attrs) {
        if (attrs == null)
            return null;

        final var buffer = attrs.kvs().stream()
                .map(kvp -> FT("$0$1$2", kvp._1(), ARROW_DELIM,
                        kvp._2() instanceof String ? kvp._2() : Json.obj2json(kvp._2())))
                .collect(Collectors.joining("\n"));

        return buffer;
    }

    /**
     * 属性调整
     * 
     * @param file json 数据文件
     * @return
     */
    public IRecord jsonfile2attrs(final File file) {
        final var pattern = Pattern.compile("([^\\.]+)\\..*");
        var name = file.getName();
        final var matcher = pattern.matcher(name);
        if (matcher.matches())
            name = matcher.group(1);
        final var attributes = REC("name", name);
        final Function<String, Object> fmt = line -> {
            if (line.startsWith("[")) {
                final var oo = Json.json2list(line, List.class);
                return oo;
            } else {
                return line;
            }
        };

        FileSystem.utf8lines(file).map(e -> e.split(ARROW_DELIM, 2))
                .forEach(e -> attributes.add(e[0].trim(), fmt.apply(e[1].trim())));
        this.adjust_attrs(attributes);
        println(name, attributes);
        return attributes;
    }

    /**
     * 属性调整
     * 
     * @param attrs 属性集合
     * @return
     */
    public IRecord adjust_attrs(final IRecord attrs) {
        final var name = attrs.str("name");
        final var en = attrs.computeIfAbsent("en", key -> {
            return name == null ? null : getPinYin(name);
        });
        final var price = attrs.get("price", "1"); // 价格
        final var ldt = LocalDateTime.now().format(DTF);
        final var alias = attrs.get("alias", attrs.get("en", name));

        attrs.add("alias", alias);
        attrs.add("en", en);
        attrs.add("price", price);
        attrs.add("createtime", ldt);

        return attrs;
    }

    /**
     * 覆盖导入
     * 
     * @param db   数据库名
     * @param home 数据源目录
     */
    @Test
    public void import_overwrite(final String db, final String home) {
        final var attributeList = new LinkedList<IRecord>();
        final var url = FT(
                "jdbc:mysql://localhost:3306/$0?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True", db);
        final var dataApp = new DataApp() {
        }.reload2("url", url);

        FileSystem.tranverse(new File(FT("$0", home)), file -> {
            final var attributes = this.jsonfile2attrs(file);
            final var name = attributes.get("name");
            println(attributes, attributes);
            attributeList.add(REC("name", name, "Attributes", attributes));
        });

        attributeList.forEach(e -> {
            println(e);
        });

        dataApp.withTransactionX(DFrame.dfmclc, sess -> {
            for (final var rec : attributeList) {
                final var name = rec.str("name");
                final var exists_sql = FT("select * from t_vertex where name='$0'", name);
                final var opt = sess.sql2maybe(exists_sql);

                if (opt.isEmpty()) {
                    final var json = rec.rec("Attributes").json().replace("'", "\\'"); // 引号转义
                    final var sql = FT("insert into t_vertex(name,Attributes) values ('$0','$1')", rec.str("name"),
                            json);
                    println(sql);
                    sess.sql2execute(sql);
                } else { // 属性的更新
                    final var _rec = opt.get();
                    final var id = _rec.i4("id");
                    final var _name = _rec.str("name");
                    final var attrs = rec.rec("Attributes");
                    final var _attrs = _rec.rec("Attributes");
                    final var flag = attrs.equals(_attrs);
                    // println(flag, "==", attrs, "==", _attrs);
                    if (attrs == null || attrs.size() < 1) {
                        continue;
                    } else {
                        if (flag) {
                            continue;
                        } // if
                        println(flag, "==", _name, "==", attrs.json(), "==", _attrs.json());
                    } // if
                    final Function<Object, String> fmt = line -> {
                        if (line instanceof List) {
                            final var ll = (List<?>) line;
                            final var array = ll.stream().map(s -> FT("\"$0\"", s)).collect(Collectors.joining(","));
                            return FT("JSON_ARRAY($0)", array);
                        } else {
                            return FT("'$0'", (line + "").replace("'", "\\'"));
                        } // if
                    };
                    final var flds = attrs.stream().map(e -> FT("'$.\"$0\"',$1", e._1(), fmt.apply(e._2()))// 引号转义
                    ).collect(Collectors.joining(","));
                    final var sql = FT("update t_vertex set Attributes = JSON_SET(Attributes,$0) where id=$1", flds,
                            id);
                    println(sql);
                    sess.sql2execute(sql);
                }
            } // for
        }); // withTransactionX

        println("运行结束");
    }

    /**
     * 清空导入
     */
    @Test
    public void import_clear(final String db, final String home) {
        final var attributeList = new LinkedList<IRecord>();
        final var url = FT(
                "jdbc:mysql://localhost:3306/$0?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True", db);
        final var dataApp = new DataApp() {
        }.reload2("url", url);

        FileSystem.tranverse(new File(FT("$0", home)), file -> {
            final var attributes = this.jsonfile2attrs(file);
            final var name = attributes.get("name");
            println(attributes, attributes);
            attributeList.add(REC("name", name, "Attributes", attributes));
        });

        dataApp.withTransactionX(DFrame.dfmclc, sess -> {
            sess.sqlexecute("delete from t_vertex");
            for (final var rec : attributeList) {
                final var json = rec.rec("Attributes").json().replace("'", "\\'");
                final var sql = FT("insert into t_vertex(name,Attributes) values ('$0','$1')", rec.str("name"), json);
                println(sql);
                sess.sql2execute(sql);
            } // for
        }); // withTransactionX

        println("运行结束");
    }

    /**
     * 扁平导出
     * 
     * @param db   数据库名
     * @param home 导出位置
     */
    @Test
    public void export_flat(String db, final String home) {
        final var url = "jdbc:mysql://localhost:3306/graph?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True";
        final var dataApp = new DataApp() {
        }.reload2("url", url);

        dataApp.withTransactionX(DFrame.dfmclc, sess -> {
            final var dfm = sess.sql2u("select * from t_vertex");
            dfm.rowsS().forEach(e -> {
                final var name = e.str("name");
                final var attributes = e.rec("Attributes");

                println(name, attributes);
                FileSystem.write(FT("$0/$1.txt", home, name), attrs2jsonfile(attributes));
            });
        });

        println("运行结束");
    }

    /**
     * 创建树形解结构
     * 
     * @param db   数据库名
     * @param home 导出位置
     */
    @Test
    public void export_tree(final String db, final String home) {
        final var url = FT(
                "jdbc:mysql://localhost:3306/$0?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True", db);
        final var dataApp = new DataApp() {
        }.reload2("url", url);

        dataApp.withTransactionX(DFrame.dfmclc, sess -> {
            final var rootrec = REC("id", "-1", "name", "root", "pid", null);
            final var sql = "select v.id,v.name, ifnull(e.v0,-1) pid , v.Attributes vattr, e.Attributes eattr "
                    + "from t_vertex v left join t_edge e on v.id = e.v1";
            final var id_name_pid_list = sess.sql2records(sql); // 提取属性结构的 三字段接哦古
            id_name_pid_list.add(rootrec);
            final var rootNode = LittleTree.buildTree(id_name_pid_list, -1); // 生成节点树形结构

            rootNode.forEach(e -> {
//                final var line = FT("$0$1", " | ".repeat(e.getLevel() - 1), e.getName());
//                final var attr = e.getValue(r -> {
//                    final var rec = REC("vattr", r.rec("vattr"), "eattr", r.rec("eattr"));
//                    return rec;
//                }); // attr
//                println(line, attr);
                final var noderec = e.getValue();
                if (!e.getName().equals(rootrec.get("name"))) { // 取消掉
                    var vattr = noderec.rec("vattr");
                    if (vattr == null)
                        vattr = REC();
                    println(e.getName(), vattr);
                    FileSystem.write(FT("$0/$1.txt", home, e.getPath()), attrs2jsonfile(vattr));
                } // if
            }); // forEach
        });

    }

    /**
     * 数据表导出
     */
    @Test
    public void export_db_tbl() {
        final var db = "erp"; // 数据库
        final var basehome = "F:\\slicef\\desktop\\tmp\\wksdata\\2021\\0915"; // 基准目录
        final var jsons_home = FT("$0\\data", basehome); // json 文件家目录
        final var url = FT(
                "jdbc:mysql://localhost:3306/$0?useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=True", db);
        final var dataApp = new DataApp() {
        }.reload2("url", url);

        dataApp.withTransactionX(DFrame.dfmclc, sess -> {
            final var sql = "select * from t_fortune"; // 数据文件检索
            final var rootid = -1;
            final var rootrec = REC("id", rootid, "name", "root", "pid", null); // 根节点编号
            final var id_name_pid_list = sess.sql2recordS(sql) // 提取数据项目
                    .map(e -> e.filter("id,name") // 提取节点 id 和 节点名称
                            .derive("pid", rootid, "vattr", e) // 注意添加 pid 节点作为 以便构成 树形结构
            ).collect(Collectors.toList()); // 生成三字段项目记录 (id,name,pid)

            // 显示数据结构
            println(id_name_pid_list.stream().collect(DFrame.dfmclc));
            id_name_pid_list.add(rootrec);

            final var rootNode = LittleTree.buildTree(id_name_pid_list, -1); // 生成节点树形结构
            rootNode.forEach(e -> {
                final var noderec = e.getValue();
                if (!e.getName().equals(rootrec.get("name"))) { // 取消掉
                    var vattr = noderec.rec("vattr");
                    if (vattr == null)
                        vattr = REC();
                    println(e.getName(), vattr);
                    FileSystem.write(FT("$0/jsons/$1.txt", jsons_home, e.getPath()), attrs2jsonfile(vattr));
                } // if
            }); // forEach
        });

    }

    /**
     * 
     * @param src_home  源目录
     * @param dest_home 目标目录
     * @param action    回调函数
     */
    @Test
    public void prepare_data(String src_home, String dest_home, Consumer<IRecord> action) {
        final var pattern = Pattern.compile("([^\\.]+)\\..*");

        FileSystem.tranverse(new File(FT("$0", src_home)), file -> {
            var name = file.getName();
            final var matcher = pattern.matcher(name);
            if (matcher.matches()) {
                name = matcher.group(1);
            }

            println(name);

            final var ss = name.split(",");
            final var alias = ss.length > 1 ? ss[0] : null;
            final var filename = ss.length > 1 ? ss[1] : ss[0];
            final var attrs = REC("name", filename, "path",
                    file.getAbsoluteFile().getAbsolutePath().replace("\\", "/"));
            if (alias != null) {
                attrs.add("alias", alias);
            } // if
            if (action != null) {
                action.accept(attrs);
            }
            final var path = FT("$0/$1.txt", dest_home, filename);

            FileSystem.write(path, attrs2jsonfile(attrs));
        });
    }

    /**
     * 数据准备
     */
    @Test
    public void prepare_data() {
        final var src_home = "C:\\Users\\Administrator\\Pictures\\菜品\\火锅";
        final var dest_home = "D:/sliced/tmp/graph/火锅";

        this.prepare_data(src_home, dest_home, null);
    }

    /**
     * 数据准备
     */
    @Test
    void prepare_data2() {
        final var basehome = "F:\\slicef\\desktop\\doc\\ws\\CHINA";
        final var excel = SimpleExcel.of(FT("$0\\CHINA.xlsx", basehome));
        final var dfm = excel.autoDetect(0).collect(DFrame.dfmclc2);
        final var createTime = LocalDateTime.now();

        dfm.rowsS().forEach(row -> {
            final var name = row.str("name").replace(" ", "");
            final var home = FT("$0\\$1", basehome, name);
            final var datafile = FT("$0\\$1.txt", home, name); // 数据文件
            final var attrs = REC("createtime", createTime.format(DTF));

            println("datafile:", datafile);
            FileSystem.utf8write(datafile, () -> this.attrs2jsonfile(attrs));
        });

    }

    /**
     * 数据导出
     */
    @Test
    public void export_db() {
        final var home = "D:/sliced/tmp/graph/vertex2";
        this.export_tree("graph", home);
    }

    /**
     * 数据导入
     */
    @Test
    public void import_db() {
        final var b = true; // 是否采用覆盖模式
        var home = "D:/sliced/tmp/graph/medicine/Endocrine Physiology/hormone";
        home = "D:/sliced/tmp/graph/vertex2";
        home = "D:/sliced/tmp/graph/药典/头孢";
        home = "D:\\sliced\\tmp\\graph\\火锅";

        if (b) {
            this.import_overwrite("klcgraph", home);
        } else {
            this.import_clear("klcgraph", home);
        } // if
    }

    /**
     * 
     * @param line
     * @return
     */
    public static String getPinYin(final String line) {
        HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
        // 拼音小写
        format.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        // 不带声调
        format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
        // 要转换的中文，格式，转换之后的拼音的分隔符，遇到不能转换的是否保留 wo,shi,zhong,guo,ren,，hello
        var pingyin = "-";
        try {
            pingyin = PinyinHelper.toHanYuPinyinString(line, format, "", true);
        } catch (BadHanyuPinyinOutputFormatCombination e) {
            e.printStackTrace();
        }
        
        return pingyin;
    }

    final static String ARROW_DELIM = ": ";
    final static DateTimeFormatter DTF = CronTime.dtf("yyyy-MM-dd HH:mm:ss");

}
